from flask import Flask, render_template, request, redirect, url_for, flash
import sqlite3
import datetime

app = Flask(__name__)
app.secret_key = 'your_secret_key'  # 用于flash消息

# 初始化数据库
def init_db():
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    
    # 创建任务表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            status TEXT NOT NULL DEFAULT 'pending',
            priority TEXT NOT NULL DEFAULT 'medium',
            due_date TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL
        )
    ''')
    
    # 插入一些示例任务（如果表为空）
    cursor.execute('SELECT COUNT(*) FROM tasks')
    count = cursor.fetchone()[0]
    
    if count == 0:
        now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        tomorrow = (datetime.datetime.now() + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
        day_after_tomorrow = (datetime.datetime.now() + datetime.timedelta(days=2)).strftime('%Y-%m-%d')
        
        sample_tasks = [
            ('完成项目文档', '编写项目的README和使用说明文档', 'pending', 'high', tomorrow, now, now),
            ('学习Flask框架', '了解Flask的基本用法和路由系统', 'pending', 'medium', day_after_tomorrow, now, now),
            ('创建数据库表', '设计和创建项目所需的数据库表结构', 'completed', 'high', now.split(' ')[0], now, now)
        ]
        
        cursor.executemany('INSERT INTO tasks (title, description, status, priority, due_date, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)', sample_tasks)
    
    conn.commit()
    conn.close()

# 获取所有任务
def get_all_tasks(status_filter=None, sort_by=None):
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    
    query = 'SELECT id, title, description, status, priority, due_date, created_at, updated_at FROM tasks'
    params = []
    
    # 添加状态筛选
    if status_filter and status_filter != 'all':
        query += ' WHERE status = ?'
        params.append(status_filter)
    
    # 添加排序
    if sort_by == 'priority':
        # 按优先级排序：high > medium > low
        query += ' ORDER BY CASE priority WHEN "high" THEN 1 WHEN "medium" THEN 2 WHEN "low" THEN 3 END, due_date ASC'
    elif sort_by == 'due_date':
        query += ' ORDER BY due_date ASC'
    else:
        query += ' ORDER BY created_at DESC'
    
    cursor.execute(query, params)
    tasks = cursor.fetchall()
    conn.close()
    return tasks

# 获取单个任务
def get_task_by_id(task_id):
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    cursor.execute('SELECT id, title, description, status, priority, due_date, created_at, updated_at FROM tasks WHERE id = ?', (task_id,))
    task = cursor.fetchone()
    conn.close()
    return task

# 添加新任务
def add_task(title, description, status, priority, due_date):
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    cursor.execute('INSERT INTO tasks (title, description, status, priority, due_date, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?)', 
                  (title, description, status, priority, due_date, now, now))
    conn.commit()
    conn.close()

# 更新任务
def update_task(task_id, title, description, status, priority, due_date):
    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    cursor.execute('UPDATE tasks SET title = ?, description = ?, status = ?, priority = ?, due_date = ?, updated_at = ? WHERE id = ?', 
                  (title, description, status, priority, due_date, now, task_id))
    conn.commit()
    conn.close()

# 删除任务
def delete_task(task_id):
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    cursor.execute('DELETE FROM tasks WHERE id = ?', (task_id,))
    conn.commit()
    conn.close()

# 获取任务统计信息
def get_task_stats():
    conn = sqlite3.connect('tasks.db')
    cursor = conn.cursor()
    
    # 计算总任务数
    cursor.execute('SELECT COUNT(*) FROM tasks')
    total_tasks = cursor.fetchone()[0]
    
    # 计算各状态任务数
    cursor.execute('SELECT status, COUNT(*) FROM tasks GROUP BY status')
    status_counts = dict(cursor.fetchall())
    
    # 计算逾期任务数
    today = datetime.datetime.now().strftime('%Y-%m-%d')
    cursor.execute('SELECT COUNT(*) FROM tasks WHERE status != "completed" AND due_date < ? AND due_date IS NOT NULL', (today,))
    overdue_tasks = cursor.fetchone()[0]
    
    conn.close()
    
    # 确保所有状态都有计数
    for status in ['pending', 'in_progress', 'completed']:
        if status not in status_counts:
            status_counts[status] = 0
    
    return {
        'total_tasks': total_tasks,
        'status_counts': status_counts,
        'overdue_tasks': overdue_tasks
    }

# 主页路由\@app.route('/')
def index():
    # 获取筛选和排序参数
    status_filter = request.args.get('status', 'all')
    sort_by = request.args.get('sort', None)
    
    # 获取任务列表
    tasks = get_all_tasks(status_filter, sort_by)
    
    # 获取统计信息
    stats = get_task_stats()
    
    return render_template('index.html', tasks=tasks, status_filter=status_filter, sort_by=sort_by, stats=stats)

# 新建任务路由\@app.route('/new', methods=['GET', 'POST'])
def new_task():
    if request.method == 'POST':
        title = request.form['title'].strip()
        description = request.form['description'].strip()
        status = request.form['status']
        priority = request.form['priority']
        due_date = request.form['due_date'] if request.form['due_date'] else None
        
        if not title:
            flash('任务标题不能为空！')
            return redirect(url_for('new_task'))
        
        add_task(title, description, status, priority, due_date)
        flash('任务创建成功！')
        return redirect(url_for('index'))
    
    return render_template('edit.html', task=None, action='new')

# 编辑任务路由\@app.route('/edit/<int:task_id>', methods=['GET', 'POST'])
def edit_task(task_id):
    task = get_task_by_id(task_id)
    
    if not task:
        flash('任务不存在！')
        return redirect(url_for('index'))
    
    if request.method == 'POST':
        title = request.form['title'].strip()
        description = request.form['description'].strip()
        status = request.form['status']
        priority = request.form['priority']
        due_date = request.form['due_date'] if request.form['due_date'] else None
        
        if not title:
            flash('任务标题不能为空！')
            return redirect(url_for('edit_task', task_id=task_id))
        
        update_task(task_id, title, description, status, priority, due_date)
        flash('任务更新成功！')
        return redirect(url_for('index'))
    
    return render_template('edit.html', task=task, action='edit')

# 删除任务路由\@app.route('/delete/<int:task_id>')
def delete_task_route(task_id):
    task = get_task_by_id(task_id)
    
    if not task:
        flash('任务不存在！')
    else:
        delete_task(task_id)
        flash('任务删除成功！')
    
    return redirect(url_for('index'))

# 启动时初始化数据库
if __name__ == '__main__':
    init_db()
    print('简易任务管理系统已启动！访问 http://localhost:5000 开始使用。')
    app.run(debug=True)